import datetime
import time
import pyodbc
import MSLogger; log = MSLogger.get('wsgi_server')
import MSEvent
import MSDatetime

class Reader:
    def __init__(self, host, port, dbname, user, pwd):
        self.host = host
        self.port = port
        self.dbname = dbname
        self.user = user
        self.pwd  = pwd
        self.connection = None

    def getName(self):
        return '(MSSQL:%s:%s:%s:%s)' % (str(self.host), str(self.port), str(self.dbname), str(self.user))

    def getConnection(self):
        log.debug('%s::connecting..', self.getName())
        return pyodbc.connect(driver='{SQL Server}', server=self.host, port=self.port, database=self.dbname, uid=self.user, pwd=self.pwd)

    def isConnected(self):
        if self.connection: return True
        return False

    def connect(self):
        if self.isConnected():
            return True

        attemptCount = 0
        while True:
            try:
                self.connection = self.getConnection()
                break
            except Exception, err:
                secs = 2
                attemptCount += 1
                if attemptCount > 3:
                    attemptCount = 0
                    secs *= 10
                log.info('%s::connection failed - sleeping %d secs', self.getName(), secs)
                time.sleep(secs)
                continue
        return True

    def getCursor(self):
        self.connect()
        return self.connection.cursor()

    def normalizeDatetime(self, d):
        return d.strftime(MSDatetime.TIME_FORMAT_MAGICDB)
    def normalizeTimerange(self, timerange):
        return (self.normalizeDatetime(timerange[0]), self.normalizeDatetime(timerange[1]))

    def parseEventPGSM(self, EventTuple):
        e = {}
        e['devid']      = EventTuple[0]
        e['datetime']   = EventTuple[1].strftime(MSDatetime.TIME_FORMAT_MAGIC_API)
        e['type']       = EventTuple[2]
        e['id']         = EventTuple[3]
        return e

    def parseEventContactId(self, EventTuple):
        e = {}
        cid = ''
        for i in EventTuple[2]:
            t = str(hex(ord(i)))
            if len(t) == 3:
                t = '0%s' % (t[2])
            else:
                t = '%s%s' % (t[2], t[3])
            cid += t
        e['devid']  = EventTuple[0]
        e['value']  = cid
        e['date']   = EventTuple[1].strftime(MSDatetime.TIME_FORMAT_MAGIC_API)
        e['id']     = EventTuple[3]
        return e

    def parseEventBolid(self, EventTuple):
        e = {}
        e['devid']      = EventTuple[0]
        e['datetime']   = EventTuple[1].strftime(MSDatetime.TIME_FORMAT_MAGIC_API)
        e['type']       = EventTuple[2]
        e['id']         = EventTuple[3]
        return e

    def parseEventGPS(self, EventTuple):
        e = {}
        e['devid']  = EventTuple[0]
        e['date']   = EventTuple[1].strftime(MSDatetime.TIME_FORMAT_MAGIC_API)
        e['id']     = EventTuple[2]
        e['lat']    = EventTuple[3]
        e['lon']    = EventTuple[4]
        e['speed']  = EventTuple[5]
        return e

    def getEvents_PGSM_TIMED(self, obj, timerange):
        cursor      = self.getCursor()
        query       = "SELECT deviceId,eventTime,type,id FROM _event_data_internal WHERE eventTime>'%s' AND deviceId=%s" % (str(timerange[0]), str(obj.storage.meta['msId']))
        cursor.execute(query)
        data        = cursor.fetchall()
        events      = []
        for s in data:
            try:
                e = MSEvent.Event()
                t = self.parseEventPGSM(s)
                e.fromPGSM(t)
                events.append(e)
            except Exception, err:
                log.error('%s::getEvents_PGSM_TIMED::%s', self.getName(), str(err))
                continue

        if len(events) > 0: obj.append(events, True)
        log.debug('%s::getEvents_PGSM_TIMED: %d events for query %s', self.getName(), len(events), query)
        return len(events)

    def getEvents_ContactId_TIMED(self, obj, timerange):
        cursor      = self.getCursor()
        query       = "SELECT deviceId,eventTime,contactData,eventId FROM _contactId_data_internal WHERE eventTime>'%s' AND deviceId=%s" % (str(timerange[0]), str(obj.storage.meta['msId']))
        cursor.execute(query)
        data        = cursor.fetchall()
        events      = []
        for s in data:
            try:
                e = MSEvent.Event()
                t = self.parseEventContactId(s)
                e.fromContactId(t)
                events.append(e)
            except Exception, err:
                log.error('%s::getEvents_ContactId_TIMED::%s', self.getName(), str(err))
                continue

        if len(events) > 0: obj.append(events, True)
        log.debug('%s::getEvents_ContactId_TIMED:: %d events for query %s', self.getName(), len(events), query)
        return len(events)

    def getEvents_Bolid_TIMED(self, obj, timerange):
        cursor      = self.getCursor()
        query       = "SELECT deviceId,eventTime,rs232str,eventId FROM _rs232_data WHERE eventTime>'%s' AND deviceId=%s" % (str(timerange[0]), str(obj.storage.meta['msId']))
        cursor.execute(query)
        data        = cursor.fetchall()
        events      = []
        for s in data:
            try:
                e = MSEvent.Event()
                t = self.parseEventBolid(s)
                e.fromBolid(t)
                events.append(e)
            except Exception, err:
                log.error('%s::getEvents_Bolid_TIMED::%s', self.getName(), str(err))
                continue

        if len(events) > 0: obj.append(events, True)
        return len(events)

    def getEvents_GPS_TIMED(self, obj, timerange):
        cursor      = self.getCursor()
        query       = "SELECT deviceId,eventTime,eventId,latitude,longitude,speed FROM _dps_data_internal WHERE eventTime>'%s' AND deviceId=%s" % (str(timerange[0]), str(obj.storage.meta['msId']))
        cursor.execute(query)
        data        = cursor.fetchall()
        events      = []
        for s in data:
            try:
                e = MSEvent.Event()
                t = self.parseEventGPS(s)
                e.fromGPSPoint(t)
                events.append(e)
            except Exception, err:
                log.error('%s::getEvents_GPS_TIMED::%s', self.getName(), str(err))
                continue

        if len(events) > 0: obj.append(events, True)
        return len(events)

    def getEvents_TODAY(self, obj):
        timerange = self.normalizeTimerange(MSDatetime.getToday())
        num =   self.getEvents_PGSM_TIMED(obj, timerange)
        num +=  self.getEvents_ContactId_TIMED(obj, timerange)
        #self.getEvents_Bolid_TIMED(obj, timerange)
        #self.getEvents_GPS_TIMED(obj, timerange)
        return num

    def getEvents_TIMED(self, obj, starttime):
        timerange = self.normalizeTimerange(MSDatetime.getToday())
        timerange = (self.normalizeDatetime(starttime), timerange[1])
        num =   self.getEvents_PGSM_TIMED(obj, timerange)
        num +=  self.getEvents_ContactId_TIMED(obj, timerange)
        #self.getEvents_Bolid_TIMED(obj, timerange)
        #self.getEvents_GPS_TIMED(obj, timerange)
        return num

